const mysql = require('mysql2/promise');
require('dotenv').config();

// 数据库连接配置
const dbConfig = {
  host: process.env.DB_HOST,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME,
  port: process.env.DB_PORT || 3306
};

// 初始化数据库表和数据
const initDatabase = async () => {
  let connection;
  
  try {
    connection = await mysql.createConnection(dbConfig);
    console.log('✅ 数据库连接成功');
    
    // 创建所有表
    await createTables(connection);
    
    // 插入初始数据
    await insertInitialData(connection);
    
    console.log('🎉 数据库初始化完成');
  } catch (error) {
    console.error('❌ 数据库初始化失败:', error.message);
  } finally {
    if (connection) {
      await connection.end();
    }
  }
};

// 创建所有表
const createTables = async (connection) => {
  console.log('📝 开始创建数据库表...');
  
  // 素材分类表
  await connection.execute(`
    CREATE TABLE IF NOT EXISTS material_types (
      id INT AUTO_INCREMENT PRIMARY KEY,
      name VARCHAR(100) NOT NULL COMMENT '分类名称',
      description TEXT COMMENT '分类描述',
      sort_order INT DEFAULT 0 COMMENT '排序',
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='素材分类表'
  `);
  
  // 素材表
  await connection.execute(`
    CREATE TABLE IF NOT EXISTS materials (
      id INT AUTO_INCREMENT PRIMARY KEY,
      name VARCHAR(200) NOT NULL COMMENT '素材名称',
      type_id INT COMMENT '分类ID',
      url VARCHAR(500) NOT NULL COMMENT '素材URL',
      thumbnail VARCHAR(500) COMMENT '缩略图URL',
      width INT COMMENT '宽度',
      height INT COMMENT '高度',
      file_size INT COMMENT '文件大小(字节)',
      file_type VARCHAR(50) COMMENT '文件类型',
      tags TEXT COMMENT '标签(JSON格式)',
      is_public BOOLEAN DEFAULT TRUE COMMENT '是否公开',
      download_count INT DEFAULT 0 COMMENT '下载次数',
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      FOREIGN KEY (type_id) REFERENCES material_types(id) ON DELETE SET NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='素材表'
  `);
  
  // 用户素材表
  await connection.execute(`
    CREATE TABLE IF NOT EXISTS user_materials (
      id INT AUTO_INCREMENT PRIMARY KEY,
      user_id VARCHAR(100) COMMENT '用户ID',
      name VARCHAR(200) NOT NULL COMMENT '素材名称',
      type_id INT COMMENT '分类ID',
      url VARCHAR(500) NOT NULL COMMENT '素材URL',
      thumbnail VARCHAR(500) COMMENT '缩略图URL',
      width INT COMMENT '宽度',
      height INT COMMENT '高度',
      file_size INT COMMENT '文件大小(字节)',
      file_type VARCHAR(50) COMMENT '文件类型',
      tags TEXT COMMENT '标签(JSON格式)',
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      FOREIGN KEY (type_id) REFERENCES material_types(id) ON DELETE SET NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户素材表'
  `);
  
  // 模板分类表
  await connection.execute(`
    CREATE TABLE IF NOT EXISTS template_types (
      id INT AUTO_INCREMENT PRIMARY KEY,
      name VARCHAR(100) NOT NULL COMMENT '分类名称',
      description TEXT COMMENT '分类描述',
      sort_order INT DEFAULT 0 COMMENT '排序',
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='模板分类表'
  `);
  
  // 模板表
  await connection.execute(`
    CREATE TABLE IF NOT EXISTS templates (
      id INT AUTO_INCREMENT PRIMARY KEY,
      name VARCHAR(200) NOT NULL COMMENT '模板名称',
      type_id INT COMMENT '分类ID',
      preview VARCHAR(500) COMMENT '预览图URL',
      thumbnail VARCHAR(500) COMMENT '缩略图URL',
      width INT COMMENT '宽度',
      height INT COMMENT '高度',
      json_data LONGTEXT COMMENT '模板JSON数据',
      tags TEXT COMMENT '标签(JSON格式)',
      is_public BOOLEAN DEFAULT TRUE COMMENT '是否公开',
      download_count INT DEFAULT 0 COMMENT '下载次数',
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      FOREIGN KEY (type_id) REFERENCES template_types(id) ON DELETE SET NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='模板表'
  `);
  
  // 字体样式分类表
  await connection.execute(`
    CREATE TABLE IF NOT EXISTS font_style_types (
      id INT AUTO_INCREMENT PRIMARY KEY,
      name VARCHAR(100) NOT NULL COMMENT '分类名称',
      description TEXT COMMENT '分类描述',
      sort_order INT DEFAULT 0 COMMENT '排序',
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='字体样式分类表'
  `);
  
  // 字体样式表
  await connection.execute(`
    CREATE TABLE IF NOT EXISTS font_styles (
      id INT AUTO_INCREMENT PRIMARY KEY,
      name VARCHAR(200) NOT NULL COMMENT '样式名称',
      type_id INT COMMENT '分类ID',
      preview VARCHAR(500) COMMENT '预览图URL',
      font_family VARCHAR(100) COMMENT '字体族',
      font_size INT COMMENT '字体大小',
      font_weight VARCHAR(20) COMMENT '字体粗细',
      color VARCHAR(20) COMMENT '颜色',
      style_data TEXT COMMENT '样式数据(JSON格式)',
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      FOREIGN KEY (type_id) REFERENCES font_style_types(id) ON DELETE SET NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='字体样式表'
  `);
  
  // 字体表
  await connection.execute(`
    CREATE TABLE IF NOT EXISTS fonts (
      id INT AUTO_INCREMENT PRIMARY KEY,
      name VARCHAR(200) NOT NULL COMMENT '字体名称',
      font_family VARCHAR(100) NOT NULL COMMENT '字体族',
      font_url VARCHAR(500) COMMENT '字体文件URL',
      preview_text VARCHAR(200) DEFAULT 'AaBbCc123' COMMENT '预览文本',
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='字体表'
  `);
  
  // 字体边框表
  await connection.execute(`
    CREATE TABLE IF NOT EXISTS font_borders (
      id INT AUTO_INCREMENT PRIMARY KEY,
      name VARCHAR(200) NOT NULL COMMENT '边框名称',
      border_style VARCHAR(50) COMMENT '边框样式',
      border_width INT COMMENT '边框宽度',
      border_color VARCHAR(20) COMMENT '边框颜色',
      preview VARCHAR(500) COMMENT '预览图URL',
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='字体边框表'
  `);
  
  // 尺寸表
  await connection.execute(`
    CREATE TABLE IF NOT EXISTS sizes (
      id INT AUTO_INCREMENT PRIMARY KEY,
      name VARCHAR(100) NOT NULL COMMENT '尺寸名称',
      width INT NOT NULL COMMENT '宽度',
      height INT NOT NULL COMMENT '高度',
      category VARCHAR(50) COMMENT '分类',
      is_default BOOLEAN DEFAULT FALSE COMMENT '是否默认',
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='尺寸表'
  `);
  
  // 横幅表
  await connection.execute(`
    CREATE TABLE IF NOT EXISTS banners (
      id INT AUTO_INCREMENT PRIMARY KEY,
      title VARCHAR(200) NOT NULL COMMENT '标题',
      description TEXT COMMENT '描述',
      image_url VARCHAR(500) COMMENT '图片URL',
      link_url VARCHAR(500) COMMENT '链接URL',
      position VARCHAR(50) DEFAULT 'home' COMMENT '位置',
      sort_order INT DEFAULT 0 COMMENT '排序',
      is_active BOOLEAN DEFAULT TRUE COMMENT '是否激活',
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='横幅表'
  `);
  
  // 网站信息表
  await connection.execute(`
    CREATE TABLE IF NOT EXISTS web_site (
      id INT AUTO_INCREMENT PRIMARY KEY,
      site_name VARCHAR(200) DEFAULT 'YX Design' COMMENT '网站名称',
      site_description TEXT COMMENT '网站描述',
      site_keywords VARCHAR(500) COMMENT '网站关键词',
      logo_url VARCHAR(500) COMMENT 'Logo URL',
      favicon_url VARCHAR(500) COMMENT 'Favicon URL',
      contact_email VARCHAR(100) COMMENT '联系邮箱',
      contact_phone VARCHAR(50) COMMENT '联系电话',
      address TEXT COMMENT '地址',
      copyright VARCHAR(200) COMMENT '版权信息',
      icp_number VARCHAR(100) COMMENT 'ICP备案号',
      analytics_code TEXT COMMENT '统计代码',
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='网站信息表'
  `);
  
  console.log('✅ 数据库表创建完成');
};

// 插入初始数据
const insertInitialData = async (connection) => {
  console.log('📝 开始插入初始数据...');
  
  // 插入素材分类
  await connection.execute(`
    INSERT IGNORE INTO material_types (id, name, description, sort_order) VALUES
    (1, '图片', '各种图片素材', 1),
    (2, '图标', '图标素材', 2),
    (3, '背景', '背景图片', 3),
    (4, '装饰', '装饰元素', 4),
    (5, '其他', '其他类型素材', 99)
  `);
  
  // 插入模板分类
  await connection.execute(`
    INSERT IGNORE INTO template_types (id, name, description, sort_order) VALUES
    (1, '海报', '海报模板', 1),
    (2, '名片', '名片模板', 2),
    (3, '传单', '传单模板', 3),
    (4, '社交媒体', '社交媒体模板', 4),
    (5, '其他', '其他类型模板', 99)
  `);
  
  // 插入字体样式分类
  await connection.execute(`
    INSERT IGNORE INTO font_style_types (id, name, description, sort_order) VALUES
    (1, '标题', '标题字体样式', 1),
    (2, '正文', '正文字体样式', 2),
    (3, '装饰', '装饰字体样式', 3),
    (4, '其他', '其他字体样式', 99)
  `);
  
  // 插入常用尺寸
  await connection.execute(`
    INSERT IGNORE INTO sizes (id, name, width, height, category, is_default) VALUES
    (1, 'A4纸张', 794, 1123, '纸张', true),
    (2, 'A3纸张', 1123, 1587, '纸张', false),
    (3, '名片', 354, 212, '名片', false),
    (4, '海报', 1080, 1920, '海报', false),
    (5, '横幅', 1920, 600, '横幅', false),
    (6, '正方形', 1080, 1080, '社交媒体', false),
    (7, 'Instagram故事', 1080, 1920, '社交媒体', false),
    (8, 'Facebook封面', 1640, 859, '社交媒体', false)
  `);
  
  // 插入网站信息
  await connection.execute(`
    INSERT IGNORE INTO web_site (id, site_name, site_description, site_keywords, copyright) VALUES
    (1, 'YX Design', '专业的在线设计平台，提供丰富的设计模板和素材', '设计,在线设计,图片编辑,模板,素材', '© 2024 YX Design. All rights reserved.')
  `);
  
  console.log('✅ 初始数据插入完成');
};

// 运行初始化
if (require.main === module) {
  initDatabase();
}

module.exports = { initDatabase };